Amazon Redshift Spectrum がスカラーJSONおよびIonデータ型をサポートしたので試してみました

Amazon Redshift Spectrum がスカラーJSONおよびIonデータ型をサポートしたので試してみました

#Redshift Spectrum
#Amazon Redshift
#AWS
石川覚

石川覚

facebook logohatena logotwitter logo
Clock Icon2018.03.12

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

はじめに

Amazon Redshift Spectrumによって、Amazon S3の外部テーブルに格納されているスカラーJSONおよびIONデータ型を直接読み込むことができるようになりましたので、早速試してみました。クラスタバージョンは、1.0.1865 です。

スカラーJSONおよびIONデータ型

JSONは、非同期のブラウザとサーバー間の通信に一般的に使用されるオープンスタンダードのファイル形式です。Ionは、データを長期間保存するための明確なセマンティクスを提供するために、ほぼ10年前にAmazonによって開発されたデータ直列化形式です。Ionテキスト形式はJSONのスーパーセットです。したがって、有効なJSONドキュメントは有効なIonドキュメントでもあります。

JSONのシリアライズ-デシリアライズには、OpenX Technologies, IncのJsonSerdeを用いています。

英語マニュアル通りを試してみるが...

執筆時点では、日本語マニュアルには記載がありませんので、英語マニュアルのCREATE EXTERNAL TABLEを参考にします。

example には、以下のcloudtrailのテーブル定義が記載されています。

create external table spectrum.cloudtrail_json (
event_version int
event_id bigint,
event_time timestamp,
event_type varchar(10),
awsregion varchar(20),
event_name varchar(max),
event_source varchar(max),
requesttime timestamp,
useragent varchar(max),
recipientaccountid bigint)
row format serde 'org.openx.data.jsonserde.JsonSerDe'
with serdeproperties (
'dots.in.keys' = 'true',
'mapping.requesttime' = 'requesttimestamp'
) location 's3://mybucket/json/cloudtrail';

実際にcloudtrailのログファイルを指定すると、

cmdb=> create external table spectrum.cloudtrail_json (
cmdb(> event_version int,
cmdb(> event_id bigint,
cmdb(> event_time timestamp,
cmdb(> event_type varchar(10),
cmdb(> awsregion varchar(20),
cmdb(> event_name varchar(max),
cmdb(> event_source varchar(max),
cmdb(> requesttime timestamp,
cmdb(> useragent varchar(max),
cmdb(> recipientaccountid bigint)
cmdb-> row format serde 'org.openx.data.jsonserde.JsonSerDe'
cmdb-> with serdeproperties (
cmdb(> 'dots.in.keys' = 'true',
cmdb(> 'mapping.requesttime' = 'requesttimestamp'
cmdb(> ) location 's3://mybucket/cloudtrail-logs';
CREATE EXTERNAL TABLE
Time: 707.142 ms
cmdb=> select * from spectrum.cloudtrail_json limit 1;
ERROR: S3 Query Exception (Fetch)
DETAIL:
-----------------------------------------------
error: S3 Query Exception (Fetch)
code: 15001
context: Task failed due to an internal error. Error occured during Ion/JSON extractor match: IERR_UNEXPECTED_EOF

query: 82836
location: dory_util.cpp:682
process: query0_112 [pid=17599]
-----------------------------------------------

Time: 1783.787 ms (00:01.784)

マニュアル通りではうまくいかないようなので、以降、試行錯誤してみました。

検証１

以下のAWSが公開しているサンプルデータ試してみました。timersがネストしたJSONファイルになっています。

$ aws s3 cp s3://us-east-1.elasticmapreduce/samples/hive-ads/tables/impressions/dt=2009-04-12-13-00/ec2-0-51-75-39.amazon.com-2009-04-12-13-00.log - | head -n 1 | jq .
{
"number": "92583",
"referrer": "paipai.com",
"processId": "1655",
"adId": "FwTr8BJ6949s6V9a4Eb4bNhAkKrSCe",
"browserCookie": "skxmlacilk",
"userCookie": "EskxUTIcilSmJ3cg2ncsrRHSijGxCl",
"requestEndTime": "1239541873000",
"impressionId": "RsR89fR0BJNbuEFBiw3dq4EJBu8B0I",
"userAgent": "Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.1.1) Gecko/20090715 Firefox/3.5.1",
"timers": {
"modelLookup": "0.3498",
"requestTime": "0.666"
},
"threadId": "14",
"ip": "23.231.246.62",
"modelId": "bxxiuxduab",
"hostname": "ec2-0-51-75-39.amazon.com",
"sessionId": "2wnKWfrVuDgF7BqWlVgNpD4rsjDv3f",
"requestBeginTime": "1239541873000"
}

Amazon Athenaのテーブル定義を参考に、テーブル定義してみるとstructの<でエラーが発生します。CREATE EXTERNAL TABLEのシンタックスにSTRUCT、MAP、ARRAY、UNIONなどはありませんので、ネストされたJSONファイル形式はサポートされていないことが確認できます。

cmdb=> CREATE EXTERNAL TABLE .impressions_10(
cmdb(> requestbegintime varchar(max),
cmdb(> adid varchar(max),
cmdb(> impressionid varchar(max),
cmdb(> referrer varchar(max),
cmdb(> useragent varchar(max),
cmdb(> usercookie varchar(max),
cmdb(> ip varchar(max),
cmdb(> number varchar(max),
cmdb(> processid varchar(max),
cmdb(> browsercookie varchar(max),
cmdb(> requestendtime varchar(max),
cmdb(> timers struct<modellookup:varchar(max),requesttime:varchar(max)>,
cmdb(> threadid varchar(max),
cmdb(> hostname varchar(max),
cmdb(> sessionid varchar(max))
cmdb-> PARTITIONED BY (
cmdb(> dt varchar(max))
cmdb-> ROW FORMAT SERDE
cmdb-> 'org.openx.data.jsonserde.JsonSerDe'
cmdb-> WITH SERDEPROPERTIES (
cmdb(> 'paths'='requestBeginTime, adId, impressionId, referrer, userAgent, userCookie, ip')
cmdb-> STORED AS INPUTFORMAT
cmdb-> 'org.apache.hadoop.mapred.TextInputFormat'
cmdb-> OUTPUTFORMAT
cmdb-> 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
cmdb-> LOCATION
cmdb-> 's3://us-east-1.elasticmapreduce/samples/hive-ads/tables/impressions'
cmdb-> ;
ERROR: syntax error at or near "<"
LINE 13: timers struct<modellookup:varchar(max),requesttime:varchar... ^ Time: 303.305 ms

更にtimersを文字列としてテーブル定義してみると、テーブル定義は成功しました。

cmdb=> CREATE EXTERNAL TABLE .impressions_11(
cmdb(> requestbegintime varchar(max),
cmdb(> adid varchar(max),
cmdb(> impressionid varchar(max),
cmdb(> referrer varchar(max),
cmdb(> useragent varchar(max),
cmdb(> usercookie varchar(max),
cmdb(> ip varchar(max),
cmdb(> number varchar(max),
cmdb(> processid varchar(max),
cmdb(> browsercookie varchar(max),
cmdb(> requestendtime varchar(max),
cmdb(> timers varchar(max),
cmdb(> threadid varchar(max),
cmdb(> hostname varchar(max),
cmdb(> sessionid varchar(max))
cmdb-> PARTITIONED BY (
cmdb(> dt varchar(max))
cmdb-> ROW FORMAT SERDE
cmdb-> 'org.openx.data.jsonserde.JsonSerDe'
cmdb-> WITH SERDEPROPERTIES (
cmdb(> 'paths'='requestBeginTime, adId, impressionId, referrer, userAgent, userCookie, ip')
cmdb-> STORED AS INPUTFORMAT
cmdb-> 'org.apache.hadoop.mapred.TextInputFormat'
cmdb-> OUTPUTFORMAT
cmdb-> 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
cmdb-> LOCATION
cmdb-> 's3://us-east-1.elasticmapreduce/samples/hive-ads/tables/impressions'
cmdb-> ;
CREATE EXTERNAL TABLE
Time: 463.905 ms

データファイルはカラム名ありパーティション形式なので、Amazon AthenaからMSCK REPAIR TABLEを実行して、パーティションを自動認識させます。ここまではうまくいきました。

データを参照してみましたが、Nested datatype is not supported for this file format.とある通り、ネストされたデータ型はサポートできないことが確認できました。

cmdb=> SELECT * FROM .impressions_11 LIMIT 1;
ERROR: S3 Query Exception (Fetch)
DETAIL:
-----------------------------------------------
error: S3 Query Exception (Fetch)
code: 15001
context: Task failed due to an internal error. Nested datatype is not supported for this file format.

query: 83016
location: dory_util.cpp:682
process: query1_119 [pid=17131]
-----------------------------------------------

Time: 3120.225 ms (00:03.120)

一方、ネストされていないカラムは問題なく表示できました。

cmdb=> SELECT ip FROM .impressions_11 LIMIT 1;
ip
-------------
70.95.51.46
(1 row)

Time: 8400.512 ms (00:08.401)

検証２

ネストされたJSONファイルは、サポートしていないようなので以下のフラットなJSONファイルを作成しました。

{"id":"1","name":"foo","age":"10"}
{"id":"2","name":"bar","age":"20"}
{"id":"3","name":"baz","age":"30"}

全て文字列型のカラムとしてテーブル定義します。

cmdb=> CREATE EXTERNAL TABLE .users_flatjson(
cmdb(> id varchar(max),
cmdb(> name varchar(max),
cmdb(> age varchar(max)
cmdb(> )
cmdb-> ROW FORMAT SERDE
cmdb-> 'org.openx.data.jsonserde.JsonSerDe'
cmdb-> -- WITH SERDEPROPERTIES (
cmdb-> -- 'paths'='id, name, age')
cmdb-> STORED AS INPUTFORMAT
cmdb-> 'org.apache.hadoop.mapred.TextInputFormat'
cmdb-> OUTPUTFORMAT
cmdb-> 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
cmdb-> LOCATION
cmdb-> 's3://mybucket/users_flatjson/'
cmdb-> ;
CREATE EXTERNAL TABLE
Time: 486.978 ms

やっと、何のエラーもなくクエリが成功しました。

cmdb=> SELECT * FROM .users_flatjson;
id | name | age
----+------+-----
1 | foo | 10
2 | bar | 20
3 | baz | 30
(3 rows)

Time: 3148.414 ms (00:03.148)

なお、idカラムを INT型で定義すると、以下のようなエラーが表示されました。恐らく文字列型にのみ定義できるようでした。

cmdb=> CREATE EXTERNAL TABLE .users_flatjson_2(
cmdb(> id int,
cmdb(> name varchar(max),
cmdb(> age int
cmdb(> )
cmdb-> ROW FORMAT SERDE
cmdb-> 'org.openx.data.jsonserde.JsonSerDe'
cmdb-> STORED AS INPUTFORMAT
cmdb-> 'org.apache.hadoop.mapred.TextInputFormat'
cmdb-> OUTPUTFORMAT
cmdb-> 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
cmdb-> LOCATION
cmdb-> 's3://mybucket/users_flatjson/'
cmdb-> ;
CREATE EXTERNAL TABLE
Time: 604.839 ms
cmdb=> SELECT * FROM .users_flatjson_2;
ERROR: S3 Query Exception (Fetch)
DETAIL:
-----------------------------------------------
error: S3 Query Exception (Fetch)
code: 15001
context: Task failed due to an internal error. In file https://s3.amazonaws.com/mybucket/users_flatjson/users_flatjson.json declared column type INT for column STRING is incompatible

query: 83410
location: dory_util.cpp:682
process: query0_69 [pid=17373]
-----------------------------------------------

Time: 3918.127 ms (00:03.918)

最後に

スカラーJSONおよびIonデータ型をサポートは、ネストしていないフラットなJSONのみのサポートであることがわかりました。re:Invent2017で発表のあった Nested Data Supportかと期待しましたが、今回のアップデートはこれとは違うようです。Nested Data Support がサポートされたらまた改めて検証したいと思います。

Share this article

facebook logohatena logotwitter logo

関連記事

dbtで外部テーブルを参照するモデルを作成する

dbtで外部テーブルを参照するモデルを作成する

クラスメソッド データアナリティクス通信(AWSデータ分析編) – 2023年12月号

クラスメソッド データアナリティクス通信(AWSデータ分析編) – 2023年12月号

User avatar
石川覚
2023.11.27
[小ネタ]S3バケットを出力先に設定しているRedshift監査ログをRedshift Spectrumで分析しようとして少しハマったこと

[小ネタ]S3バケットを出力先に設定しているRedshift監査ログをRedshift Spectrumで分析しようとして少しハマったこと

User avatar
suto
2022.11.18
[Athena] OpenCSVSerDe使用時のデータ型の挙動をまとめてみた

[Athena] OpenCSVSerDe使用時のデータ型の挙動をまとめてみた

User avatar
平野重利
2022.07.19
Classmethod's white logo
Facebook's logo
X's logo
YouTube's logo

© Classmethod, Inc. All rights reserved.